package com.FindMyStuff.Model;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataAccessor implements IDataAccessor {

	/**
	 * Holds the DatabaseHelper
	 */
	private SQLiteOpenHelper dbHelper;

	public DataAccessor(SQLiteOpenHelper databaseHelper) {
		dbHelper = databaseHelper;
	}

	/**
	 * Opens the database in order to write or read
	 * @return
	 * @throws SQLException
	 */
	private SQLiteDatabase open() throws SQLException {
		return dbHelper.getWritableDatabase();
	}

	/**
	 * Closes the database after execution is done
	 */
	private void close() {
		dbHelper.close();
	}

	@Override
	public User getUserByEmail(String email) {
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return null;
		}
		Cursor cursor = db.query(DataBaseHelper.userTable,
				DataBaseHelper.userAllColumns, DataBaseHelper.userColumnEmail
						+ "== '" + email + "'", null, null, null, null);
		if (cursor.moveToFirst() == false) {
			return new NullUser();
		}
		User res = cursorToUser(cursor);
		close();
		cursor.close();
		return res;
	}

	@Override
	public void updateUserStatus(User user, UserStatus newStatus) {
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return;
		}
		ContentValues values = new ContentValues();
		values.put(DataBaseHelper.userColumnStatus, newStatus.toString());
		db.update(
				DataBaseHelper.userTable,
				values,
				DataBaseHelper.userColumnEmail + "== '" + user.getEmail() + "'",
				null);
		close();
	}

	@Override
	public User createUser(String email, String password, UserRole role) {
		SQLiteDatabase db;
		long error;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return null;
		}

		ContentValues values = new ContentValues();
		values.put(DataBaseHelper.userColumnEmail, email);
		values.put(DataBaseHelper.userColumnPassword, password);
		values.put(DataBaseHelper.userColumnRole, role.toString());
		values.put(DataBaseHelper.userColumnStatus, "u");
		values.put(DataBaseHelper.userColumnAttempts, 3);
		error = db.insert(DataBaseHelper.userTable, null, values);
		db.close();
		if (error == -1) {
			return new NullUser();
		}
		return new User(email, password, role);
	}

	/**
	 * Helper function to change a cursor from reading the database into a user
	 * 
	 * @param cursor
	 *            cursor from database read
	 * @return User read user from cursor
	 */
	private static User cursorToUser(Cursor cursor) {
		String username = cursor.getString(0);
		String password = cursor.getString(1);
		UserRole role = UserRole.convertToRole(cursor.getString(2));
		UserStatus status = UserStatus.convertToStatus(cursor.getString(3));
		int attempts = cursor.getInt(4);
		return new User(username, password, role, status, attempts);
	}

	/**
	 * Updates the attempts left by the user in the database
	 * @param user to update
	 */
	public void logInSuccess(User user) {
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return;
		}
		user.logInSucess();
		ContentValues values = new ContentValues();
		values.put(DataBaseHelper.userColumnAttempts, user.loginAttemptsLeft);
		db.update(
				DataBaseHelper.userTable,
				values,
				DataBaseHelper.userColumnEmail + "== '" + user.getEmail() + "'",
				null);
		close();
	}

	/**
	 * Updates the attempts left by the user in the database
	 * @param user to update
	 */
	public void logInFailed(User user) {
		if (user.logInFailed()) {
			updateUserStatus(user, UserStatus.Locked);
		}
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return;
		}
		ContentValues values = new ContentValues();
		values.put(DataBaseHelper.userColumnAttempts, user.loginAttemptsLeft);
		db.update(DataBaseHelper.userTable, values,
				DataBaseHelper.userColumnEmail + " == '" + user.getEmail()
						+ "'", null);
		close();
	}
	
	/**
	 * Unlocks the user
	 * @param user to update
	 */
	public void unlockUser(User user) {
		user.loginAttemptsLeft = 3;
		updateUserStatus(user, UserStatus.Unlocked);
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return;
		}
		ContentValues values = new ContentValues();
		values.put(DataBaseHelper.userColumnAttempts, user.loginAttemptsLeft);
		db.update(DataBaseHelper.userTable, values,
				DataBaseHelper.userColumnEmail + " == '" + user.getEmail()
						+ "'", null);
		close();
	}
	
	/**
	 * Locks the user
	 * @param user to update
	 */
	public void lockUser(User user) {
		user.loginAttemptsLeft = 0;
		updateUserStatus(user, UserStatus.Locked);
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return;
		}
		ContentValues values = new ContentValues();
		values.put(DataBaseHelper.userColumnAttempts, user.loginAttemptsLeft);
		db.update(DataBaseHelper.userTable, values,
				DataBaseHelper.userColumnEmail + " == '" + user.getEmail()
						+ "'", null);
		close();
	}
	
	@SuppressLint("SimpleDateFormat")
	@Override
	public Item createItem(String name, String description,
		String location, ItemCategory category, Double reward, ItemType type) {
		SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
		Date dateHolder = new Date();
		String date = sdf.format(dateHolder);
		SQLiteDatabase db;
		long error;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return new NullItem();
		}

		ContentValues values = new ContentValues();
		SessionManager sm = SessionManager.getInstance();
		values.put(DataBaseHelper.itemName, name);
		values.put(DataBaseHelper.itemDescription, description);
		values.put(DataBaseHelper.itemLocation, location);
		values.put(DataBaseHelper.itemCategory, category.toString());
		values.put(DataBaseHelper.itemReward, reward.toString());
		values.put(DataBaseHelper.itemStatus, ItemStatus.Open.toString());
		values.put(DataBaseHelper.itemType, type.toString());
		values.put(DataBaseHelper.itemDate, date);
		values.put(DataBaseHelper.itemOwner, sm.getCurrentUser().getEmail());
		error = db.insert(DataBaseHelper.itemTable, null, values);
		db.close();
		if(error == -1)
		{
			return new NullItem();
		}
		return new Item(-1, name, description, location,  ItemStatus.Open ,category,reward, type, dateHolder, sm.getCurrentUser().getEmail());

	}

	@Override
	public ArrayList<Item> getItemsByUser(User user, ItemType type) { //Old retrun - Item[]
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return null;
		}
		Cursor cursor = db.query(DataBaseHelper.itemTable,
				DataBaseHelper.itemAllColumns, DataBaseHelper.itemOwner
						+ "== '" + user.getEmail() + "'" + " and "
						+ DataBaseHelper.itemType + " == '" + type + "'", null,
				null, null, null);
		if (cursor.moveToFirst() == false) {
			return new ArrayList<Item>();
		}
		ArrayList<Item> res = cursorToItems(cursor);
		close();
		cursor.close();
		return res;
	}

	/**
	 * Helper Method to convert the database results into an array of Items
	 * @param cursor database results
	 * @return array of items from database
	 */
	@SuppressLint("SimpleDateFormat")
	private ArrayList<Item> cursorToItems(Cursor cursor) {//private Item[] cursorToItems(Cursor cursor) {
		ArrayList<Item> res = new ArrayList<Item>();
		SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
		cursor.moveToFirst();
		while (cursor.isAfterLast() == false) {
			int id = cursor.getInt(0);
			String name = cursor.getString(1);
			String description = cursor.getString(2);
			String location = cursor.getString(3);
			ItemStatus status = ItemStatus.convertToStatus(cursor.getString(4));
			ItemCategory category = ItemCategory.convertToCategory(cursor
					.getString(5));
			Double reward = cursor.getDouble(6);
			ItemType type = ItemType.convertToType(cursor.getString(7));
			Date date;
			try {
				date = sdf.parse(cursor.getString(8));
			} catch (ParseException e) {
				System.out.println("UNABLE TO PARSE STRING INTO DATE FOR ITEM!!!!");
				date = new Date();
			}
			String owner = cursor.getString(9);
			Item add = new Item(id, name, description, location, status,
					category, reward, type, date, owner);
			res.add(add);
			cursor.moveToNext();
		}
		return res; 
	}

	@Override
	public boolean removeUser(String email) {
		SQLiteDatabase db;
		int res;
		try 
		{
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return false;
		}
		db.delete(DataBaseHelper.itemTable, DataBaseHelper.itemOwner + " =?", new String[] {email});
		res = db.delete(DataBaseHelper.userTable, DataBaseHelper.userColumnEmail + " =?" , new String[] {email});
		if(res == 1)
		{
			return true;
		}
		return false;
	}

	@Override
	public ArrayList<Item> getItemsByUser(User user) {
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return null;
		}
		Cursor cursor = db.query(DataBaseHelper.itemTable,
				DataBaseHelper.itemAllColumns, DataBaseHelper.itemOwner
						+ "== '" + user.getEmail() + "'", null,
				null, null, null);
		if (cursor.moveToFirst() == false) {
			return new ArrayList<Item>();
			
		}
		ArrayList<Item> res = cursorToItems(cursor);
		close();
		cursor.close();
		return res;
	}
	
	@Override
	public ArrayList<Item> getItemsByName(String name) {
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return null;
		}
		Cursor cursor = db.query(DataBaseHelper.itemTable,
				DataBaseHelper.itemAllColumns, DataBaseHelper.itemName
						+ " like '%" + name + "%'", null,
				null, null, null);
		if (cursor.moveToFirst() == false) {
			return new ArrayList<Item>();
			
		}
		ArrayList<Item> res = cursorToItems(cursor);
		close();
		cursor.close();
		return res;
	}
	
	@Override
	public Item getItemByNameAndLocation(String name, String location, ItemType type) {
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return null;
		}
		Cursor cursor = db.query(DataBaseHelper.itemTable,
				DataBaseHelper.itemAllColumns, DataBaseHelper.itemName
						+ " like '%" + name + "%' and " + DataBaseHelper.itemLocation + " like '%" + location + "%' " +
						"and " + DataBaseHelper.itemType + " == '" + type + "'", null,
				null, null, null);
		if (cursor.moveToFirst() == false) {
			return new NullItem();
			
		}
		Item res = cursorToItem(cursor);
		close();
		cursor.close();
		return res;
	}
	
	@SuppressLint("SimpleDateFormat")
	private Item cursorToItem(Cursor cursor) {
		SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy");
		cursor.moveToFirst();
		int id = cursor.getInt(0);
		String name = cursor.getString(1);
		String description = cursor.getString(2);
		String location = cursor.getString(3);
		ItemStatus status = ItemStatus.convertToStatus(cursor.getString(4));
		ItemCategory category = ItemCategory.convertToCategory(cursor
				.getString(5));
		Double reward = cursor.getDouble(6);
		ItemType type = ItemType.convertToType(cursor.getString(7));
		Date date;
		try {
			date = sdf.parse(cursor.getString(8));
		} catch (ParseException e) {
			System.out.println("UNABLE TO PARSE STRING INTO DATE FOR ITEM!!!!");
			date = new Date();
		}
		String owner = cursor.getString(9);
		return new Item(id, name, description, location, status,
				category, reward, type, date, owner);
	}

	@Override
	public ArrayList<Item> getItemsByName(String name, ItemCategory cat) {
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return null;
		}
		Cursor cursor = db.query(DataBaseHelper.itemTable,
				DataBaseHelper.itemAllColumns, DataBaseHelper.itemName
						+ " like '%" + name + "%' and " +DataBaseHelper.itemCategory + " == '"
						+ cat + "'", null,
				null, null, null);
		if (cursor.moveToFirst() == false) {
			return new ArrayList<Item>();
			
		}
		ArrayList<Item> res = cursorToItems(cursor);
		close();
		cursor.close();
		return res;
	}

	@Override
	public ArrayList<Item> getAllItems() {
		SQLiteDatabase db;
		try {
			db = open();
		} catch (SQLException ex) {
			System.out.println("SQL Database Could Not be Opened");
			return null;
		}
		Cursor cursor = db.query(DataBaseHelper.itemTable,
				DataBaseHelper.itemAllColumns, DataBaseHelper.itemCategory + " in ('h', 'k', 'o')", null,
				null, null, null);
		if (cursor.moveToFirst() == false) {
			return new ArrayList<Item>();
			
		}
		ArrayList<Item> res = cursorToItems(cursor);
		close();
		cursor.close();
		return res;
	}
}
